Analyzing Healthcare Provider Shortage - Part 3/4

In this part of the study, we will explore shortage of Mental Healthcare Providers and OB-GYN Providers. We will study the distribution of:

  • Providers across all states to identify which states have the most shortage and
  • for those states study shortage at the county level to understand which counties are suffering the most

Part 3: Provider Shortage - Mental Health and OB-GYN Providers

In this section, we will:

  • Explore Mental Healthcare Providers
    • Study how Mental Healthcare Providers vary by Population Density accross all states
    • Identify state with highest people to provider ratio
    • Explore the state with highest ratio to understand how providers vary across different counties for that state
  • Explore OBGYN Healthcare Providers
    • Study how OBGYN Healthcare Providers vary by population of mothers accross all states
    • Identify state with highest mothers to provider ratio
    • Explore the state with highest ratio to understand how providers vary across different counties for that state
In [2]:
# Import Libraries
from IPython.display import display

# Import arcgis
import arcgis
from arcgis.gis import GIS
from arcgis.features import FeatureLayer
from arcgis.mapping import WebMap

# Import libraries for data exploration
import pandas as pd
pd.set_option('display.max_columns', 500)
import numpy as np

# Import plotting libraries
%matplotlib inline
import matplotlib.pyplot as plt
import seaborn as sns

# Import library for time
import time
In [3]:
# Create a GIS connection
gis = GIS("https://datascienceqa.esri.com/portal", "portaladmin", "esri.agp", verify_cert=False)
# gis = GIS("https://datascienceqa.esri.com/portal", "portaladmin", "esri.agp")

Get Data Layers

Provider data was geocoded using the GeoAnalytics server. We will get the geocoded provider data feature layer. We will also get demographic data from the 2018 USA Population Density layer.

Get Provider Data

In [7]:
# Search the feature layer
search_result = gis.content.search('title: provider_data_geocoded_7_30', 'Feature Layer')
search_result
Out[7]:
[]
In [5]:
# Get the feature layer item
provider_data_item = search_result[0]
provider_data_item
Out[5]:
provider_data_geocoded_7_30
geocoded healthcare provider dataFeature Layer Collection by portaladmin
Last Modified: September 13, 2019
0 comments, 2 views
In [6]:
# Check for layers inside the item
provider_data_item.layers
Out[6]:
[<FeatureLayer url:"https://datascienceqa.esri.com/server/rest/services/Hosted/provider_data_geocoded_7_30/FeatureServer/0">]
In [7]:
# Get the layer needed for analysis
provider_data_layer = provider_data_item.layers[0]
provider_data_layer
Out[7]:
<FeatureLayer url:"https://datascienceqa.esri.com/server/rest/services/Hosted/provider_data_geocoded_7_30/FeatureServer/0">
In [7]:
# Look at the first 5 fields and their data types
for f in provider_data_layer.properties.fields[:5]:
    print(f['name'],'      ',f['type'])
objectid        esriFieldTypeOID
user_npi        esriFieldTypeDouble
user_entity_type        esriFieldTypeString
user_address        esriFieldTypeString
user_address2        esriFieldTypeString

Get Demographics Data

In [6]:
# Search for Population data layer
# popsearch_result = gis.content.search('title: 2018 USA Population Density')
popsearch_result = gis.content.get('ab4e1996d588405d9cd68348ef660f70')
popsearch_result
Out[6]:
2018 USA Population Density
This layer shows the population density in the United States in 2018 in persons per square mile in a multiscale map by country, state, county, ZIP Code, tract, and block group. ArcGIS Online subscription required.Map Image Layer by esri_livingatlas
Last Modified: October 18, 2019
0 comments, 0 views
In [9]:
# Get Population Density
# popdensity = popsearch_result[0]
popdensity = popsearch_result
popdensity
Out[9]:
2018 USA Population Density
This layer shows the population density in the United States in 2018 in persons per square mile in a multiscale map by country, state, county, ZIP Code, tract, and block group. ArcGIS Online subscription required.Map Image Layer by esri_livingatlas
Last Modified: October 18, 2019
0 comments, 0 views
In [10]:
# Check first 5 layers in population Density
popdensity.layers[:5]
Out[10]:
[<FeatureLayer url:"https://datascienceqa.esri.com/portal/sharing/servers/ab4e1996d588405d9cd68348ef660f70/rest/services/USA_Demographics_and_Boundaries_2018/MapServer/0">,
 <FeatureLayer url:"https://datascienceqa.esri.com/portal/sharing/servers/ab4e1996d588405d9cd68348ef660f70/rest/services/USA_Demographics_and_Boundaries_2018/MapServer/1">,
 <FeatureLayer url:"https://datascienceqa.esri.com/portal/sharing/servers/ab4e1996d588405d9cd68348ef660f70/rest/services/USA_Demographics_and_Boundaries_2018/MapServer/2">,
 <FeatureLayer url:"https://datascienceqa.esri.com/portal/sharing/servers/ab4e1996d588405d9cd68348ef660f70/rest/services/USA_Demographics_and_Boundaries_2018/MapServer/3">,
 <FeatureLayer url:"https://datascienceqa.esri.com/portal/sharing/servers/ab4e1996d588405d9cd68348ef660f70/rest/services/USA_Demographics_and_Boundaries_2018/MapServer/4">]
In [11]:
# Look at first few field names for county layer
county_layer = popdensity.layers[46]
print('FIELD NAME', '\t\t', 'FIELD ALIAS')
for field in county_layer.properties.fields[:10]:
    print(field['name'], '\t\t', field['alias'])
FIELD NAME 		 FIELD ALIAS
OBJECTID 		 OBJECTID
Shape 		 Shape
ID 		 ID
NAME 		 NAME
STATE_NAME 		 STATE_NAME
ST_ABBREV 		 ST_ABBREV
AREA 		 Area in Square Miles (Calculated)
TOTPOP_CY 		 2018 Total Population (Esri)
HHPOP_CY 		 2018 Household Population (Esri)
FAMPOP_CY 		 2018 Family Population (Esri)
In [9]:
# Get specific attributes for Counties
%time
county_layer = popdensity.layers[46]
county_df = pd.DataFrame()
offset = 0
while offset <= 3000:
    chunk_df = county_layer.query(out_fields=['Shape','ST_ABBREV','NAME','ASIAN_CY','AMERIND_CY','AVGHHSZ_CY','AVGHINC_CY','BLACK_CY','EDUCBASECY','HISPPOP_CY',
                          'MEDAGE_CY','MINORITYCY','OTHRACE_CY','PCI_CY','POPDENS_CY','UNEMPRT_CY','WHITE_CY','SMCOLL_CY',
                          'ASSCDEG_CY','BACHDEG_CY','GRADDEG_CY','TOTPOP_CY'],return_all_records=False,result_offset=offset,result_record_count=750,as_df=True)
    county_df = pd.concat([chunk_df, county_df], ignore_index=True)
    
    offset += 750
Wall time: 0 ns
In [10]:
county_df.shape
Out[10]:
(3142, 23)

Mental Health

Image source: https://cuindependent.com/2019/03/08/opinion-mental-health-education/

The table below shows a list of providers shortlisted as Mental healthcare providers. Provider Taxonomy codes were filtered using this reference.

Taxonomy Code Description Taxonomy Code Description
207QG0300X Family Medicine Geriatric Medicine 273R00000X Hospital Units Psychiatric Unit
103T00000X Psychologist 103TA0400X Psychologist Addiction (Substance Abuse Disorder)
103TA0700X Psychologist Adult Development & Aging 103TC0700X Psychologist Clinical
103TC2200X Psychologist Clinical Child & Adolescent 103TB0200X Psychologist Cognitive & Behavioral
103TC1900X Psychologist Counseling 103TE1000X Psychologist Educational
103TE1100X Psychologist Exercise & Sports 103TF0000X Psychologist Family
103TF0200X Psychologist Forensic 103TP2701X Psychologist Group Psychotherapy
103TH0004X Psychologist Health 103TH0100X Psychologist Health Service
103TM1700X Psychologist Men & Masculinity 103TM1800X Psychologist Mental Retardation
103TP0016X Psychologist Prescribing (Medical) 103TP0814X Psychologist Psychoanalysis
103TP2700X Psychologist Psychotherapy 103TR0400X Psychologist Rehabilitation
103TS0200X Psychologist School 103TW0100X Psychologist Women
106E00000X Assistant Behavior Analyst 106S00000X Technician
103TC0700X Psychologist Clinical 2084A0401X Physicians Psychiatry & Neurology
2084P0802X Physicians Addiction Psychiatry 2084B0002X Physicians Bariatric Medicine
2084P0804X Physicians Child & Adolescent Psychiatry 2084N0600X Physicians Clinical Neurophysiology
2084D0003X Physicians Diagnostic Neuroimaging 2084F0202X Physicians Forensic Psychiatry
2084P0805X Physicians Geriatric Psychiatry 2084H0002X Physicians Hospice & Palliative Medicine
2084P0005X Physicians Neurodevelopmental Disabilities 2084N0400X Physicians Neurology
2084N0402X Physicians Child Neurology 2084N0008X Physicians Neuromuscular Medicine
2084P2900X Physicians Pain Medicine 2084P0800X Physicians Psychiatry
2084P0015X Physicians Psychosomatic Medicine 2084S0012X Physicians Sleep Medicine
2084S0010X Physicians Sports Medicine 2084V0102X Physicians Vascular Neurology
364SA2200X Nurse/Assiatant Adult Health 364SP0808X Nurse/Assiatant Psychiatric/Mental Health
364SP0809X Nurse/Assiatant Mental Health Adult 364SP0807X Nurse/Assiatant Mental Health Child & Adolescent
364SP0810X Nurse/Assiatant Mental Health Child & Family 364SP0811X Nurse/Assiatant Mental Health Chronically Ill
364SP0812X Nurse/Assiatant Mental Health Community 364SP0813X Nurse/Assiatant Mental Health Geropsychiatric
283Q00000X Hospitals Psychiatric Hospital 261QM0801X Ambulatory Health Care Facilities/Clinic/Center Mental Health
2084P0800X Physician Psychiatry

Mental Healthcare Providers - Heat Map

Let's explore the distribution of mental healthcare providers in U.S. using a heatmap.

In [24]:
# Create a map
mental_map = gis.map('USA', 4)
mental_map
In [27]:
# Add provider data to map
renderer = {"renderer": "autocast", #This tells python to use JS autocasting
            "type": "heatmap",
            "blurRadius":1,  # changes the size of the clusters
            "maxPixelIntensity":2,
            "minPixelIntensity":0,
            "field":None}
renderer["colorStops"] = [{"ratio":0,"color":[63, 40, 102, 0]},
                          {"ratio":0.25,"color":[167,97,170,179]},
                          {"ratio":0.50,"color":"#7b3ce9"},
                          {"ratio":0.75,"color":[222,102,0,179]},
                          {"ratio":1,"color":[244,204,0,179]}]
mental_map.add_layer(provider_data_layer,
               { "type": "FeatureLayer",
                 "renderer": renderer,
                "definition_expression" : "user_taxonomy_code_1 in ('2084P0800X','207QG0300X','273R00000X','103T00000X','103TA0400X','103TA0700X','103TC0700X','103TC2200X','103TB0200X','103TC1900X','103TE1000X','103TE1100X','103TF0000X','103TF0200X','103TP2701X','103TH0004X','103TH0100X','103TM1700X','103TM1800X','103TP0016X','103TP0814X','103TP2700X','103TR0400X','103TS0200X','103TW0100X','106E00000X','106S00000X','2084A0401X','2084P0802X','2084B0002X','2084P0804X','2084N0600X','2084D0003X','2084F0202X','2084P0805X','2084H0002X','2084P0005X','2084N0400X','2084N0402X','2084N0008X','2084P2900X','2084P0015X','2084S0012X','2084S0010X','2084V0102X','364SP0808X','364SP0809X','364SP0807X','364SP0810X','364SP0811X','364SP0812X','364SP0813X','283Q00000X','261QM0801X')"
               })
In [28]:
mental_map.remove_layers()
Out[28]:
True
In [77]:
mental_map.take_screenshot()

This map paints a grim picture of the availability of mental healthcare providers. There are states in every region with vast areas of NO mental health providers or very few providers. To list, some of these states include:

  • Midwest: The Dakotas, Montana, Wyoming, Colorado, Nebraska, Kansas
  • South: Texas, New Mexico, Kansas, Louisiana, Mississippi, Alabama
  • West: Nevada, Arizona, Oregon, Idaho, Alaska
  • East: Maine, Vermont, Michigan

Population to Providers Ratio by State

Let's find out the ratio of population to mental healthcare providers to understand which states have the least number of providers.

Dataframe for Mental Healthcare providers by state

We will use provider_data_layer to subset mental healthcare providers

In [48]:
# Get provider data for mental healthcare providers only
mental_df = provider_data_layer.query(where="user_taxonomy_code_1 in ('2084P0800X','207QG0300X','273R00000X','103T00000X','103TA0400X','103TA0700X','103TC0700X','103TC2200X','103TB0200X','103TC1900X','103TE1000X','103TE1100X','103TF0000X','103TF0200X','103TP2701X','103TH0004X','103TH0100X','103TM1700X','103TM1800X','103TP0016X','103TP0814X','103TP2700X','103TR0400X','103TS0200X','103TW0100X','106E00000X','106S00000X','2084A0401X','2084P0802X','2084B0002X','2084P0804X','2084N0600X','2084D0003X','2084F0202X','2084P0805X','2084H0002X','2084P0005X','2084N0400X','2084N0402X','2084N0008X','2084P2900X','2084P0015X','2084S0012X','2084S0010X','2084V0102X','364SP0808X','364SP0809X','364SP0807X','364SP0810X','364SP0811X','364SP0812X','364SP0813X','283Q00000X','261QM0801X')", 
                          out_fields='user_npi,user_entity_type,user_provider_gender,user_taxonomy_code_1,user_full_address,postal,city,subregion,region,regionabbr', as_df=True)
# mental_df = mental_featureset.sdf
mental_df.head()
Out[48]:
objectid user_npi user_entity_type user_provider_gender user_taxonomy_code_1 user_full_address postal city subregion region regionabbr SHAPE
0 22 1.780688e+09 Individual F 103T00000X 8100 OSWEGO RD, STE 235, LIVERPOOL, NY 130901660 13090 Liverpool Onondaga County New York NY {'x': -76.23493120799998, 'y': 43.163287813000...
1 30 1.902317e+09 Organization None 2084P0800X 60 QUEENS ST STE 100, , SYOSSET, NY 117913058 11791 Syosset Nassau County New York NY {'x': -73.50383431999995, 'y': 40.824446931000...
2 44 1.962631e+09 Individual M 2084P0800X 300 CRITTENDEN BLVD, , ROCHESTER, NY 146428409 14620 Rochester Monroe County New York NY {'x': -77.62801138499998, 'y': 43.121485505000...
3 48 1.255841e+09 Individual F 103TS0200X 2212 3RD AVE FL 2, , NEW YORK, NY 100353535 10035 New York New York County New York NY {'x': -73.93801455399995, 'y': 40.801243790000...
4 74 1.689804e+09 Individual M 2084N0400X 1275 YORK AVENUE, MEMORIAL SLOAN KETTERING CAN... 10065 New York New York County New York NY {'x': -73.95588016699998, 'y': 40.763981064000...
In [62]:
# Create a csv
mental_df.to_csv(r'C:\Users\mohi9282\Desktop\arcgis\DATAFRAMES\allMentalProviders_df.csv', index=None, header=True)
In [11]:
# # Read from csv
mental_df = pd.read_csv(r'C:\Users\mohi9282\Desktop\arcgis\DATAFRAMES\allMentalProviders_df.csv', low_memory=False)
mental_df.head()
Out[11]:
objectid user_npi user_entity_type user_provider_gender user_taxonomy_code_1 user_full_address postal city subregion region regionabbr SHAPE
0 50 1.366445e+09 Individual M 2084N0400X 642 ULUKAHIKI ST, SUITE 300, KAILUA, HI 967344400 96734 Kailua City and County of Honolulu Hawaii HI {'x': -157.75842118599996, 'y': 21.38069594300...
1 110 1.760501e+09 Individual F 103T00000X 648 E GLEN OAK ST, , RIALTO, CA 923766648 92376 Rialto San Bernardino County California CA {'x': -117.35748267199995, 'y': 34.09373249300...
2 113 1.023011e+09 Individual M 103T00000X 224 E WILLAMETTE AVE, , COLORADO SPRINGS, CO 8... 80903 Colorado Springs El Paso County Colorado CO {'x': -104.81990027499995, 'y': 38.84235050400...
3 118 1.942508e+09 Organization NaN 2084P0800X 4343 W FLAGLER ST STE 100, , CORAL GABLES, FL ... 33134 Miami Miami-Dade County Florida FL {'x': -80.26657097299994, 'y': 25.771643515000...
4 122 1.508862e+09 Individual M 2084P0800X 790 GOV. CARLOS G. CAMACHO RD., , TAMUNING, GU... 96913 Tamuning NaN Guam GU {'x': 144.7754183520001, 'y': 13.5051031310000...
In [12]:
# Create dataframe of mental healthcare provider counts by state
mental_count_df = pd.DataFrame(mental_df['regionabbr'].value_counts().reset_index().values, columns=['regionabbr','Provider_Count'])
In [14]:
# Plot mental healthcare Providers by State

plt.figure(figsize=(25,12))
sns.barplot(mental_count_df['regionabbr'].iloc[:-19], mental_count_df['Provider_Count'].iloc[:-19])
plt.title('Mental Healthcare Providers by State', fontsize=22)
plt.xlabel('States', fontsize=18)
plt.ylabel('Provider Count', fontsize=18)
Out[14]:
Text(0, 0.5, 'Provider Count')

Dataframe for Population by State

We will use the population density layer at state level to create this dataframe

In [13]:
# State population dataframe
state_layer = popdensity.layers[43]
state_df = state_layer.query(out_fields='STATE_NAME,ST_ABBREV,TOTPOP_CY', as_df=True)
# state_df = state_featureset.sdf
state_df.head()
Out[13]:
OBJECTID STATE_NAME ST_ABBREV TOTPOP_CY SHAPE
0 1 Alabama AL 4968383 {'rings': [[[-9747504.6398, 3539549.5786999986...
1 2 Alaska AK 750876 {'rings': [[[-19677908.5389, 6763775.151000001...
2 3 Arizona AZ 7132147 {'rings': [[[-12138852.7978, 4438964.613399997...
3 4 Arkansas AR 3067536 {'rings': [[[-9989041.8861, 4300705.307499997]...
4 5 California CA 39806791 {'rings': [[[-13038833.5744, 3845968.519900001...

Merge Provider Count and Population Dataframes

In [14]:
# Merge provider count and population at state level
# Merge with State data on left to preserve 'polygon' geometry
state_mental_df = pd.merge(state_df,mental_count_df,right_on='regionabbr', left_on='ST_ABBREV',how='inner')
In [15]:
state_mental_df.head()
Out[15]:
OBJECTID STATE_NAME ST_ABBREV TOTPOP_CY SHAPE regionabbr Provider_Count
0 1 Alabama AL 4968383 {'rings': [[[-9747504.6398, 3539549.5786999986... AL 2030
1 2 Alaska AK 750876 {'rings': [[[-19677908.5389, 6763775.151000001... AK 678
2 3 Arizona AZ 7132147 {'rings': [[[-12138852.7978, 4438964.613399997... AZ 5237
3 4 Arkansas AR 3067536 {'rings': [[[-9989041.8861, 4300705.307499997]... AR 1507
4 5 California CA 39806791 {'rings': [[[-13038833.5744, 3845968.519900001... CA 51820
In [16]:
# Create new columns that shows people per provider
state_mental_df['people_per_prov'] = state_mental_df['TOTPOP_CY']/state_mental_df['Provider_Count']
In [17]:
# Arrange dataframe by people_per_prov descending
state_mental_df = state_mental_df.sort_values(by=['people_per_prov'], ascending=False)
state_mental_df.head()
Out[17]:
OBJECTID STATE_NAME ST_ABBREV TOTPOP_CY SHAPE regionabbr Provider_Count people_per_prov
24 25 Mississippi MS 3051594 {'rings': [[[-9952997.0803, 3545020.2216000035... MS 1108 2754.15
0 1 Alabama AL 4968383 {'rings': [[[-9747504.6398, 3539549.5786999986... AL 2030 2447.48
15 16 Iowa IA 3219046 {'rings': [[[-10154309.0285, 5388473.678900003... IA 1396 2305.91
12 13 Idaho ID 1760131 {'rings': [[[-12362299.9589, 5539778.200800002... ID 790 2228.01
43 44 Texas TX 28954616 {'rings': [[[-10822386.4159, 2999262.775600001... TX 13623 2125.42
In [21]:
# Write df to csv
# state_mental_df.to_csv(r'C:\Users\mohi9282\Desktop\arcgis\DATAFRAMES\MentalProviders_StateLevel_df.csv', index=None, header=True)
In [18]:
# # Read csv
# Read from csv
# state_mental_df = pd.read_csv(r'C:\Users\mohi9282\Desktop\arcgis\DATAFRAMES\MentalProviders_StateLevel_df.csv', low_memory=False)
# state_mental_df.head()
In [19]:
state_mental_df.spatial.geometry_type
Out[19]:
['polygon']

Plot Shortage

In [26]:
mental_shortage_map = gis.map('USA', zoomlevel=4)
mental_shortage_map

From this map we can see that, Mississippi is the worst with highest no. of people per mental healthcare provider followed by Alabama, Idaho, Texas, Arkansas and Iowa.

Define Renderer
In [21]:
# Define Renderer
esriTest = {"renderer": { #This tells python to use JS autocasting
                 "type": "classBreaks",  
                 "field":"people_per_prov",
                 "transparency":.5,
                 "minValue":1}}
In [22]:
# Define Manual Class breaks
esriTest['renderer']["classBreakInfos"] = [{
  "classMaxValue": 1000.00,
  "label": "0 - 1000.00",
  "description": "0 - 1000.00",
  "symbol": {
    "type": "esriSFS",
    "style": "esriSFSSolid",
    "color": [255,247,236,178.5],
    "outline": {
      "style": "esriSLSSolid",
      "type": "esriSLS",
      "color": [128,128,128,255],
      "width": 2
    }
  }
}, {
  "classMaxValue": 1500.00,
  "label": "1000.001 - 1500.00",
  "description": "1000.001 - 1500.00",
  "symbol": {
    "type": "esriSFS",
    "style": "esriSFSSolid",
    "color": [253,220,174,178.5],
    "outline": {
      "style": "esriSLSSolid",
      "type": "esriSLS",
      "color": [128,128,128,255],
      "width": 2
    }
  }
}, {
  "classMaxValue": 2000.00,
  "label": "1500.001 - 2000.00",
  "description": "1500.001 - 2000.00",
  "symbol": {
    "type": "esriSFS",
    "style": "esriSFSSolid",
    "color": [252,177,123,178.5],
    "outline": {
      "style": "esriSLSSolid",
      "type": "esriSLS",
      "color": [128,128,128,255],
      "width": 2
    }
  }
}, {
  "classMaxValue": 2500.00,
  "label": "2000.001 - 2500.00",
  "description": "2000.001 - 2500.00",
  "symbol": {
    "type": "esriSFS",
    "style": "esriSFSSolid",
    "color": [241,109,75,178.5],
    "outline": {
      "style": "esriSLSSolid",
      "type": "esriSLS",
      "color": [128,128,128,255],
      "width": 2
    }
  }
}, {
  "classMaxValue": 3000.00,
  "label": "2500.001 - 3000.00",
  "description": "2500.001 - 3000.00",
  "symbol": {
    "type": "esriSFS",
    "style": "esriSFSSolid",
    "color": [200,28,18,178.5],
    "outline": {
      "style": "esriSLSSolid",
      "type": "esriSLS",
      "color": [128,128,128,255],
      "width": 2
    }
  }
}]
Plot Map
In [23]:
# Plot Map using defined Renderer
mental_shortage_map.remove_layers()
state_mental_df.spatial.plot(map_widget=mental_shortage_map, renderer=esriTest['renderer'])
Out[23]:
True
In [24]:
#### FOR AUTOMATIC RENDERING - manual class breaks are not used
# state_mental_df.spatial.plot(map_widget=mental_shortage_map,
#         renderer_type='c',  # for class breaks renderer
#         method='esriClassifyNaturalBreaks',  # classification algorithm
#         class_count=6,  # choose the number of classes
#         col='people_per_prov',  # numeric column to classify
#         cmap='OrRd',  # color map to pick colors from for each class
#         alpha=0.7) # specify opacity
In [25]:
# Add Legend
mental_shortage_map.legend=True
In [199]:
mental_shortage_map.remove_layers()
Out[199]:
True

Plot Population per Mental Healthcare Provider

In [20]:
# Plot No. of People per Mental Heathcare Provider by State

plt.figure(figsize=(25,12))
sns.barplot(state_mental_df['regionabbr'], state_mental_df['people_per_prov'])
plt.title('No. of People per Mental Heathcare Provider by State', fontsize=22)
plt.xlabel('States', fontsize=18)
plt.ylabel('No. of People', fontsize=18)
plt.xticks(fontsize=15)
plt.yticks(fontsize=15)
Out[20]:
(array([   0.,  500., 1000., 1500., 2000., 2500., 3000.]),
 <a list of 7 Text yticklabel objects>)

On average, there were ~2754 people per mental healthcare provider in Mississippi compared to ~364 people per provider in DC. The difference is drastic.

Exploring Mississippi

Mississippi has the highest number of people per mental healthcare provider. Let's explore Mississippi to find out which counties have the lowest number of providers.

Dataframe for Population in Mississippi

In [27]:
# County population df
county_layer = popdensity.layers[46]
MS_pop_df = county_layer.query(where="ST_ABBREV='MS'", out_fields='ST_ABBREV,NAME,TOTPOP_CY', as_df=True)
# MS_pop_df = MS_featureset.sdf
MS_pop_df.head()
Out[27]:
OBJECTID ST_ABBREV NAME TOTPOP_CY SHAPE
0 1402 MS Adams County 31455 {'rings': [[[-10195353.0856, 3667011.907600000...
1 1403 MS Alcorn County 37398 {'rings': [[[-9838473.3692, 4163284.5766000003...
2 1404 MS Amite County 12573 {'rings': [[[-10139507.6094, 3675190.789200000...
3 1405 MS Attala County 19195 {'rings': [[[-9957952.5787, 3933319.5100999996...
4 1406 MS Benton County 8621 {'rings': [[[-9917285.3423, 4109418.0692000017...

Dataframe for Mental Healthcare Providers in Mississippi by County

In [28]:
# Get provider data for obgyn providers only
mental_MS_df = mental_df[mental_df['regionabbr']=='MS']
mental_MS_df.head()
mental_MS_df.shape
Out[28]:
(1108, 12)
In [29]:
# Create dataframe of provider counts by county
mental_MScounty_df = pd.DataFrame(mental_MS_df['subregion'].value_counts().reset_index().values, columns=['County','Provider_Count'])
mental_MScounty_df.head()
Out[29]:
County Provider_Count
0 Hinds County 274
1 Harrison County 143
2 Rankin County 81
3 Lamar County 76
4 Lauderdale County 52

Merge Provider Count and Population Dataframes for MS

In [31]:
# Merge provider count and women data at county level for ND
county_mental_df = pd.merge(MS_pop_df,mental_MScounty_df,left_on='NAME', right_on='County',how='left')
In [32]:
county_mental_df.head()
Out[32]:
OBJECTID ST_ABBREV NAME TOTPOP_CY SHAPE County Provider_Count
0 1402 MS Adams County 31455 {'rings': [[[-10195353.0856, 3667011.907600000... Adams County 7
1 1403 MS Alcorn County 37398 {'rings': [[[-9838473.3692, 4163284.5766000003... Alcorn County 17
2 1404 MS Amite County 12573 {'rings': [[[-10139507.6094, 3675190.789200000... Amite County 1
3 1405 MS Attala County 19195 {'rings': [[[-9957952.5787, 3933319.5100999996... Attala County 1
4 1406 MS Benton County 8621 {'rings': [[[-9917285.3423, 4109418.0692000017... Benton County 1
In [33]:
# Look at null values
county_mental_df[county_mental_df['Provider_Count'].isnull()]
Out[33]:
OBJECTID ST_ABBREV NAME TOTPOP_CY SHAPE County Provider_Count
6 1408 MS Calhoun County 14741 {'rings': [[[-9934762.5024, 4050466.4952000007... NaN NaN
7 1409 MS Carroll County 10761 {'rings': [[[-9995030.8747, 3985476.9218999967... NaN NaN
20 1422 MS Greene County 13703 {'rings': [[[-9844206.323, 3647602.087700002],... NaN NaN
27 1429 MS Issaquena County 1403 {'rings': [[[-10140770.3187, 3852771.566200003... NaN NaN
28 1430 MS Itawamba County 23998 {'rings': [[[-9836652.7223, 4091317.098099999]... NaN NaN
34 1436 MS Kemper County 11049 {'rings': [[[-9834842.1274, 3885894.298600003]... NaN NaN
39 1441 MS Leake County 22284 {'rings': [[[-9942963.4093, 3886235.154299997]... NaN NaN
48 1450 MS Montgomery County 10326 {'rings': [[[-9963859.1909, 3985594.638899997]... NaN NaN
51 1453 MS Noxubee County 11253 {'rings': [[[-9830021.4908, 3933636.4658999965... NaN NaN
62 1464 MS Sharkey County 4733 {'rings': [[[-10096882.6428, 3908091.835900001... NaN NaN
67 1469 MS Tallahatchie County 15196 {'rings': [[[-10011127.6731, 4050884.895499997... NaN NaN
70 1472 MS Tishomingo County 19971 {'rings': [[[-9832268.1871, 4103713.6533999965... NaN NaN
71 1473 MS Tunica County 10696 {'rings': [[[-10082866.9102, 4121279.449400000... NaN NaN
73 1475 MS Walthall County 14910 {'rings': [[[-10023259.2712, 3676463.753600001... NaN NaN
76 1478 MS Wayne County 20937 {'rings': [[[-9847815.3009, 3723726.849799998]... NaN NaN
77 1479 MS Webster County 10001 {'rings': [[[-9909506.3363, 3970154.5622999966... NaN NaN
78 1480 MS Wilkinson County 9745 {'rings': [[[-10141113.8026, 3674920.229000002... NaN NaN
80 1482 MS Yalobusha County 12813 {'rings': [[[-9996530.4196, 4050817.6086999997... NaN NaN

We can see that,

  • 18 counties in Mississippi do not have any mental healthcare provider.

We will replace NaN values for Provider Count in these counties with 1 to plot them on the map to see which counties have highest number of people per provider.

In [34]:
county_mental_df['Provider_Count'].replace(np.nan,1,inplace=True)
In [35]:
# Create new columns that shows Mental Healthcare provider by population
county_mental_df['people_per_prov'] = county_mental_df['TOTPOP_CY']/county_mental_df['Provider_Count']

Plot Shortage

In [43]:
mental_countyshortage_map = gis.map('Mississippi, USA', zoomlevel=6)
mental_countyshortage_map

We can see that,

  • Marshall County seems to be the worst with 1 provider for 37137 people. Some other counties with high population per provider are Pontotoc, Neshoba, Scott and Union.
Define Renderer
In [37]:
# Define Renderer
mentalCountyTest = {"renderer": { #This tells python to use JS autocasting
                 "type": "classBreaks",  
                 "field":"people_per_prov",
                 "transparency":.5,
                 "minValue":1}}
In [38]:
# Define Manual Class breaks
mentalCountyTest['renderer']["classBreakInfos"] = [{
  "classMaxValue": 7500.00,
  "label": "0 - 7500.00",
  "description": "0 - 7500.00",
  "symbol": {
    "type": "esriSFS",
    "style": "esriSFSSolid",
    "color": [255,247,236,178.5],
    "outline": {
      "style": "esriSLSSolid",
      "type": "esriSLS",
      "color": [128,128,128,255],
      "width": 2
    }
  }
}, {
  "classMaxValue": 15000.00,
  "label": "7500.001 - 15000.00",
  "description": "7500.001 - 15000.00",
  "symbol": {
    "type": "esriSFS",
    "style": "esriSFSSolid",
    "color": [253,220,174,178.5],
    "outline": {
      "style": "esriSLSSolid",
      "type": "esriSLS",
      "color": [128,128,128,255],
      "width": 2
    }
  }
}, {
  "classMaxValue": 22500.00,
  "label": "15000.001 - 22500.00",
  "description": "15000.001 - 22500.00",
  "symbol": {
    "type": "esriSFS",
    "style": "esriSFSSolid",
    "color": [252,177,123,178.5],
    "outline": {
      "style": "esriSLSSolid",
      "type": "esriSLS",
      "color": [128,128,128,255],
      "width": 2
    }
  }
}, {
  "classMaxValue": 30000.00,
  "label": "22500.001 - 30000.00",
  "description": "22500.001 - 30000.00",
  "symbol": {
    "type": "esriSFS",
    "style": "esriSFSSolid",
    "color": [241,109,75,178.5],
    "outline": {
      "style": "esriSLSSolid",
      "type": "esriSLS",
      "color": [128,128,128,255],
      "width": 2
    }
  }
}, {
  "classMaxValue": 37500.00,
  "label": "30000.001 - 37500.00",
  "description": "30000.001 - 37500.00",
  "symbol": {
    "type": "esriSFS",
    "style": "esriSFSSolid",
    "color": [200,28,18,178.5],
    "outline": {
      "style": "esriSLSSolid",
      "type": "esriSLS",
      "color": [128,128,128,255],
      "width": 2
    }
  }
}]
Plot Map
In [39]:
# Plot Map using defined Renderer
mental_countyshortage_map.remove_layers()
county_mental_df.spatial.plot(map_widget=mental_countyshortage_map, renderer=mentalCountyTest['renderer'])
Out[39]:
True
In [40]:
#### FOR AUTOMATIC RENDERING - manual class breaks are not used
# county_mental_df.spatial.plot(map_widget=mental_countyshortage_map,
#         renderer_type='c',  # for class breaks renderer
#         method='esriClassifyNaturalBreaks',  # classification algorithm
#         class_count=5,  # choose the number of classes
#         col='people_per_prov',  # numeric column to classify
#         cmap='OrRd',  # color map to pick colors from for each class
#         alpha=0.7 ,  # specify opacity
#        )
In [41]:
mental_countyshortage_map.legend = True
In [ ]:
# # Take Screenshot
# mental_countyshortage_map.take_screenshot(set_as_preview=True)
In [42]:
test = county_mental_df.sort_values(by='people_per_prov', ascending=False)[['NAME','TOTPOP_CY','Provider_Count','people_per_prov']]
test.head()
Out[42]:
NAME TOTPOP_CY Provider_Count people_per_prov
46 Marshall County 37137 1 37137.0
57 Pontotoc County 32007 1 32007.0
49 Neshoba County 29483 1 29483.0
61 Scott County 29144 1 29144.0
72 Union County 28734 1 28734.0

Marshall County Infographic

To visualize high level key facts about a geography and gain greater insights, various infographics can be created using ArcGIS Business Analyst. Learn more about ArcGIS Business Analyst.

The image below shows an infographic of key facts for Marshall County, MS.

To summarize, Mississippi has the highest number of people per Mental Healthcare Provider

- There were ~2754 people per mental healthcare provider in Mississippi compared to ~364 people per provider in DC
- 18 counties in Mississippi do not have any mental healthcare provider
- Marshall County seems to be the worst with 1 provider for 37137 people. Some other counties with high population per provider are Pontotoc, Neshoba, Scott and Union

Women's Health

Image source: https://www.aamc.org/news-insights/labor-pains-ob-gyn-shortage

The table below shows a list of providers shortlisted as OB-GYN providers. Provider Taxonomy codes were filtered using this reference.

Taxonomy Code Description
207V00000X Obstetrics & Gynecology
207VC0200X Critical Care Medicine
207VF0040X Female Pelvic Medicine and Reconstructive Surgery
207VX0201X Gynecologic Oncology
207VG0400X Gynecology
207VH0002X Hospice and Palliative Medicine
207VM0101X Maternal & Fetal Medicine
207VB0002X Obesity Medicine
207VX0000X Obstetrics
207VE0102X Reproductive Endocrinology
363LX0001X Nurse Practitioner-Obstetrics & Gynecology
163WR1000X Registered Nurse-Reproductive Endocrinology/Infertility
163WW0101X Registered Nurse-Women's Health Care, Ambulatory
282NW0100X General Acute Care Hospital - Women

OBGYN Providers - Heat Map

Let's explore OB-GYN providers in U.S. using a heat map

In [29]:
# Create Map
women_map = gis.map('USA', 4)
women_map
In [30]:
# Define renderer and add provider data for OBGYN providers
renderer = {"renderer": "autocast", #This tells python to use JS autocasting
            "type": "heatmap",
            "blurRadius":1,  # changes the size of the clusters
            "maxPixelIntensity":2,
            "minPixelIntensity":0,
            "field":None}
renderer["colorStops"] = [{"ratio":0,"color":[63, 40, 102, 0]},
                          {"ratio":0.25,"color":[167,97,170,179]},
                          {"ratio":0.50,"color":"#7b3ce9"},
                          {"ratio":0.75,"color":[222,102,0,179]},
                          {"ratio":1,"color":[244,204,0,179]}]
women_map.add_layer(provider_data_layer,
               { "type": "FeatureLayer",
                 "renderer": renderer,
                "definition_expression" : "user_taxonomy_code_1 in ('207V00000X','207VC0200X','207VF0040X','207VX0201X','207VG0400X','207VH0002X','207VM0101X','207VB0002X','207VX0000X','207VE0102X','363LX0001X','163WR1000X','163WW0101X','282NW0100X')"
#                 "definition_expression" : "user_taxonomy_code_1 = '207VC0200X' or user_taxonomy_code_1 = '207V00000X'"
                })
In [31]:
# Remove Layer
women_map.remove_layers()
Out[31]:
True
In [22]:
# Take Screenshot
women_map.take_screenshot()

This map paints a grim picture of the availability of OBGYN healthcare providers. We can see vast areas in Midwest and West with NO or very few OBGYN providers.

Mothers to Providers Ratio by State

In this section our goal is to identify which states have the highest mother's to providers ratio. Let's get data from the American Community Survey (ACS) about fertility in past 12 months by age of mother using ACS_Fertility_by_Age_Boundaries layer at State, County and Tract level. We will merge OBGYN providers data with Fertility data to achieve this.

Get the layers

In [47]:
# Get the Fertility Layers
# fertility_search = gis.content.search('title: ACS_Fertility_by_Age_Boundaries', 'Feature Layer')
# fertility_item = fertility_search[0]
# fertility_item.layers
fertility_search = gis.content.get('7995cd11748249fb8e23c4a5ad901dde')
fertility_item = fertility_search
fertility_item.layers
Out[47]:
[<FeatureLayer url:"https://services.arcgis.com/P3ePLMYs2RVChkJx/arcgis/rest/services/ACS_Fertility_by_Age_Boundaries/FeatureServer/0">,
 <FeatureLayer url:"https://services.arcgis.com/P3ePLMYs2RVChkJx/arcgis/rest/services/ACS_Fertility_by_Age_Boundaries/FeatureServer/1">,
 <FeatureLayer url:"https://services.arcgis.com/P3ePLMYs2RVChkJx/arcgis/rest/services/ACS_Fertility_by_Age_Boundaries/FeatureServer/2">]
In [48]:
# Define layers for State, County and Tract (Percent of women 15 to 50 who had a birth in the past 12 months)
fertility_state = fertility_item.layers[0]
fertility_county = fertility_item.layers[1]
fertility_tract = fertility_item.layers[2]

Dataframe for women population by State

We will use the fertility layer at state level to create this dataframe

In [49]:
# State population dataframe
fertility_featureset = fertility_state.query(where="B13016_001E>1")
fertility_df = fertility_featureset.sdf
fertility_df.head()
Out[49]:
OBJECTID STATENS GEOID STUSPS NAME ALAND AWATER B13016_001E B13016_001M B13016_002E B13016_002M B13016_003E B13016_003M B13016_004E B13016_004M B13016_005E B13016_005M B13016_006E B13016_006M B13016_007E B13016_007M B13016_008E B13016_008M B13016_009E B13016_009M B13016_010E B13016_010M B13016_011E B13016_011M B13016_012E B13016_012M B13016_013E B13016_013M B13016_014E B13016_014M B13016_015E B13016_015M B13016_016E B13016_016M B13016_017E B13016_017M B13016_calc_pctBirthsE B13016_calc_pctBirthsM B13016_calc_num15to19E B13016_calc_num15to19M B13016_calc_pct15to19E B13016_calc_pct15to19M B13016_calc_num20to24E B13016_calc_num20to24M B13016_calc_pct20to24E B13016_calc_pct20to24M B13016_calc_num25to29E B13016_calc_num25to29M B13016_calc_pct25to29E B13016_calc_pct25to29M B13016_calc_num30to34E B13016_calc_num30to34M B13016_calc_pct30to34E B13016_calc_pct30to34M B13016_calc_num35to39E B13016_calc_num35to39M B13016_calc_pct35to39E B13016_calc_pct35to39M B13016_calc_num40to44E B13016_calc_num40to44M B13016_calc_pct40to44E B13016_calc_pct40to44M B13016_calc_num45to50E B13016_calc_num45to50M B13016_calc_pct45to50E B13016_calc_pct45to50M Shape__Area Shape__Length SHAPE
0 1 01779775 01 AL Alabama 131174431216 4592944701 1150523 1402 58732 1690 2903 415 14484 792 18605 889 13911 884 6318 680 1790 423 721 193 1091791 1867 157636 1096 152944 1175 144825 1120 141620 1125 147991 2099 152552 2185 194223 1366 5.1 0.146698 160539 1172 1.8 0.258167 167428 1417 8.7 0.467339 163430 1430 11.4 0.534766 155531 1431 8.9 0.562386 154309 2206 4.1 0.436770 154342 2226 1.2 0.273556 194944 1380 0.4 0.098968 1.901226e+11 2.312416e+06 {'rings': [[[-9805779.6231, 3536997.5778], [-9...
1 2 01785533 02 AK Alaska 1478588231566 277723861311 174163 485 10582 662 408 125 2240 276 3190 359 2763 326 1483 270 430 99 68 40 163581 789 22202 326 22666 352 25803 411 23760 384 21123 710 21134 779 26893 421 6.1 0.379401 22610 349 1.8 0.552151 24906 447 9.0 1.096348 28993 546 11.0 1.220770 26523 504 10.4 1.213076 22606 760 6.6 1.173833 21564 785 2.0 0.453323 26961 423 0.3 0.148310 8.208934e+12 6.399688e+07 {'rings': [[[-19937057.4487, 6661392.0514], [-...
2 3 01779777 04 AZ Arizona 294198661567 1027245114 1571003 1527 85196 2315 3290 346 18209 982 24282 1113 22146 1110 12496 754 3273 324 1500 303 1485807 2427 222499 969 213748 1310 201156 1099 196251 1121 196215 2313 204729 2415 251209 1483 5.4 0.147141 225789 1029 1.5 0.153096 231957 1637 7.9 0.419714 225438 1564 10.8 0.488018 218397 1578 10.1 0.502940 208711 2433 6.0 0.354459 208002 2437 1.6 0.154673 252709 1514 0.6 0.119848 4.340581e+11 2.906214e+06 {'rings': [[[-12138854.2127, 4438965.0002], [-...
3 4 00068085 05 AR Arkansas 134768100673 2963631791 689530 1063 40901 1606 2514 379 11224 869 12726 754 8852 676 4012 546 1088 240 485 148 648629 1983 95325 724 89233 1111 86020 996 86174 898 88668 1665 91183 1443 112026 960 5.9 0.232502 97839 817 2.6 0.386776 100457 1410 11.2 0.850713 98746 1249 12.9 0.745972 95026 1124 9.3 0.702799 92680 1752 4.3 0.583413 92271 1463 1.2 0.259431 112511 971 0.4 0.131490 2.053261e+11 2.656502e+06 {'rings': [[[-9989043.0946, 4300705.9503], [-9...
4 5 01779778 06 CA California 403483182192 20484637928 9642845 3558 478458 5207 16079 831 76861 2027 120625 2303 138924 3037 87689 2301 28439 1168 9841 670 9164387 6546 1258668 1367 1302552 2268 1324982 2293 1245434 3141 1207288 5298 1256240 5262 1569223 3493 5.0 0.053932 1274747 1600 1.3 0.065170 1379413 3042 5.6 0.146432 1445607 3250 8.3 0.158202 1384358 4369 10.0 0.217082 1294977 5776 6.8 0.175101 1284679 5390 2.2 0.090442 1579064 3557 0.6 0.042407 6.500881e+11 5.274852e+06 {'rings': [[[-13198544.1255, 3897778.6019], [-...

Dataframe for OB-GYN providers by state

We will use provider_data_layer to subset OB-GYN providers

In [52]:
# Get provider data for obgyn providers only

obgyn_df = provider_data_layer.query(where="user_taxonomy_code_1 in ('207V00000X','207VC0200X','207VF0040X','207VX0201X','207VG0400X','207VH0002X','207VM0101X','207VB0002X','207VX0000X','207VE0102X','363LX0001X','163WR1000X','163WW0101X','282NW0100X')", out_fields='user_npi,user_entity_type,user_provider_gender,user_taxonomy_code_1,user_full_address,postal,city,subregion,region,regionabbr', as_df=True)
# obgyn_df = obgyn_featureset.sdf
obgyn_df.head()
Out[52]:
objectid user_npi user_entity_type user_provider_gender user_taxonomy_code_1 user_full_address postal city subregion region regionabbr SHAPE
0 260 1.659501e+09 Individual F 207V00000X 45 HUDSON AVE, , GLENS FALLS, NY 128014313 12801 Glens Falls Warren County New York NY {'x': -73.65232661699997, 'y': 43.305579428000...
1 393 1.013428e+09 Organization None 207VX0201X 11 PEEKSKILL HOLLOW RD # 204, , PUTNAM VALLEY,... 10579 Putnam Valley Putnam County New York NY {'x': -73.87456049999997, 'y': 41.333477489000...
2 455 1.841430e+09 Individual M 207V00000X 249 EMPIRE BLVD, , BROOKLYN, NY 112253402 11225 Brooklyn Kings County New York NY {'x': -73.95404266899999, 'y': 40.663727453000...
3 574 1.235132e+09 Individual M 207V00000X 335 PARRISH ST, , CANANDAIGUA, NY 144241728 14424 Canandaigua Ontario County New York NY {'x': -77.29022343299994, 'y': 42.874792656000...
4 734 1.346479e+09 Organization None 207VM0101X 901 STEWART AVE, , GARDEN CITY, NY 115304893 11530 Garden City Nassau County New York NY {'x': -73.60075714899995, 'y': 40.734165116000...
In [53]:
# Create a csv
# obgyn_df.to_csv(r'C:\Users\mohi9282\Desktop\arcgis\DATAFRAMES\allObgynProviders_df.csv', index=None, header=True)
In [55]:
obgyn_df = pd.read_csv(r'C:\Users\mohi9282\Desktop\arcgis\DATAFRAMES\allObgynProviders_df.csv', low_memory=False)
In [56]:
# Create dataframe of obgyn provider counts by state
obgyn_count_df = pd.DataFrame(obgyn_df['regionabbr'].value_counts().reset_index().values, columns=['regionabbr','Provider_Count'])
In [49]:
# Plot OB-GYN Providers by State

plt.figure(figsize=(25,12))
sns.barplot(obgyn_count_df['regionabbr'].iloc[:-8], obgyn_count_df['Provider_Count'].iloc[:-8])
plt.title('OB-GYN Providers by State', fontsize=22)
plt.xlabel('States', fontsize=18)
plt.ylabel('Provider Count', fontsize=18)
Out[49]:
Text(0, 0.5, 'Provider Count')

Merge Provider Count and Population Dataframes

In [57]:
# Merge provider count and women_df at state level
state_obgyn_df = pd.merge(fertility_df,obgyn_count_df,right_on='regionabbr', left_on='STUSPS',how='inner')
In [58]:
# Create new columns that shows provider by women pop
state_obgyn_df['women_per_prov'] = state_obgyn_df['B13016_001E']/state_obgyn_df['Provider_Count']
# Create new columns that shows provider by mother pop
state_obgyn_df['mother_per_prov'] = state_obgyn_df['B13016_002E']/state_obgyn_df['Provider_Count']
In [59]:
# Arrange dataframe by mother_per_prov descending
state_obgyn_df = state_obgyn_df.sort_values(by=['mother_per_prov'], ascending=False)
In [60]:
state_obgyn_df = state_obgyn_df.loc[:,['OBJECTID','SHAPE','regionabbr','Provider_Count','women_per_prov','mother_per_prov']]
state_obgyn_df.head()
Out[60]:
OBJECTID SHAPE regionabbr Provider_Count women_per_prov mother_per_prov
34 35 {'rings': [[[-10823452.8858, 6274958.149], [-1... ND 102 1654.82 111.863
44 45 {'rings': [[[-12139394.6091, 5012444.5138], [-... UT 526 1415.87 100.074
3 4 {'rings': [[[-9989043.0946, 4300705.9503], [-9... AR 440 1567.11 92.9568
41 42 {'rings': [[[-10749419.778, 5769979.5139], [-1... SD 137 1363.17 92.6788
12 13 {'rings': [[[-12918526.7316, 6275006.0277], [-... ID 250 1505.37 91.508
In [61]:
# Write df to csv
# state_obgyn_df.to_csv(r'C:\Users\mohi9282\Desktop\arcgis\DATAFRAMES\ObgynProviders_StateLevel_df.csv', index=None, header=True)
In [62]:
state_obgyn_df.spatial.geometry_type
Out[62]:
['polygon']

Plot Shortage

In [68]:
obgyn_shortage_map = gis.map('USA', zoomlevel=4)
obgyn_shortage_map

From this map, we can see that number of women (15 to 50) who had a birth in the past 12 months per OBGYN provider is high in Utah, North Dakota, South Dakota and Arkansas.

State Mo. of Mothers per OBGYN provider
ND 112
UT 100
AR 93
SD 93
ID 92
Define Renderer
In [64]:
# Define Renderer
stateObgynTest = {"renderer": { #This tells python to use JS autocasting
                 "type": "classBreaks",  
                 "field":"mother_per_prov",
                 "transparency":.5,
                 "minValue":1}}
In [65]:
# Define Manual Class breaks
stateObgynTest['renderer']["classBreakInfos"] = [{
  "classMaxValue": 23.00,
  "label": "0 - 23.00",
  "description": "0 - 23.00",
  "symbol": {
    "type": "esriSFS",
    "style": "esriSFSSolid",
    "color": [255,247,236,178.5],
    "outline": {
      "style": "esriSLSSolid",
      "type": "esriSLS",
      "color": [128,128,128,255],
      "width": 2
    }
  }
}, {
  "classMaxValue": 46.00,
  "label": "23.001 - 46.00",
  "description": "23.001 - 46.00",
  "symbol": {
    "type": "esriSFS",
    "style": "esriSFSSolid",
    "color": [253,220,174,178.5],
    "outline": {
      "style": "esriSLSSolid",
      "type": "esriSLS",
      "color": [128,128,128,255],
      "width": 2
    }
  }
}, {
  "classMaxValue": 69.00,
  "label": "46.001 - 69.00",
  "description": "46.001 - 69.00",
  "symbol": {
    "type": "esriSFS",
    "style": "esriSFSSolid",
    "color": [252,177,123,178.5],
    "outline": {
      "style": "esriSLSSolid",
      "type": "esriSLS",
      "color": [128,128,128,255],
      "width": 2
    }
  }
}, {
  "classMaxValue": 92.00,
  "label": "69.001 - 92.00",
  "description": "69.001 - 92.00",
  "symbol": {
    "type": "esriSFS",
    "style": "esriSFSSolid",
    "color": [241,109,75,178.5],
    "outline": {
      "style": "esriSLSSolid",
      "type": "esriSLS",
      "color": [128,128,128,255],
      "width": 2
    }
  }
}, {
  "classMaxValue": 115.00,
  "label": "92.001 - 115.00",
  "description": "92.001 - 115.00",
  "symbol": {
    "type": "esriSFS",
    "style": "esriSFSSolid",
    "color": [200,28,18,178.5],
    "outline": {
      "style": "esriSLSSolid",
      "type": "esriSLS",
      "color": [128,128,128,255],
      "width": 2
    }
  }
}]
Plot Map
In [66]:
# Plot Map using defined Renderer
obgyn_shortage_map.remove_layers()
state_obgyn_df.spatial.plot(map_widget=obgyn_shortage_map, renderer=stateObgynTest['renderer'])
Out[66]:
True
In [67]:
#### FOR AUTOMATIC RENDERING - manual class breaks are not used

# state_obgyn_df.spatial.plot(map_widget=obgyn_shortage_map,
#         renderer_type='c',  # for class breaks renderer
#         method='esriClassifyNaturalBreaks',  # classification algorithm
#         class_count=5,  # choose the number of classes
#         col='mother_per_prov',  # numeric column to classify
#         cmap='OrRd',  # color map to pick colors from for each class
#         alpha=0.7 ,  # specify opacity
#        )
In [65]:
obgyn_shortage_map.legend=True

Plot Mothers per OBGYN Provider

In [62]:
# Plot No. of Mothers (age 15 to 50) per OB-GYN Provider by State

plt.figure(figsize=(25,12))
sns.barplot(state_obgyn_df['regionabbr'], state_obgyn_df['mother_per_prov'])
plt.title('No. of Mothers (age 15 to 50) per OB-GYN Provider by State', fontsize=22)
plt.xlabel('States', fontsize=18)
plt.ylabel('No. of Mothers (age 15 to 50)', fontsize=18)
plt.xticks(fontsize=15)
plt.yticks(fontsize=15)
Out[62]:
(array([  0.,  20.,  40.,  60.,  80., 100., 120.]),
 <a list of 7 Text yticklabel objects>)

On average, there were ~112 mothers and 1655 women per provider in ND compared to ~31 mothers and 662 women per provider in DC. The difference is drastic.

Exploring North Dakota

North Dakota has the highest number of mothers and women per OB-GYN provider. From the fertility map above, we also saw that percent of women (15 to 50) who had a birth in the past 12 months is high in North Dakota.

Let's explore North Dakota to find out which counties have the lowest number of OBGYN providers.

Dataframe for women population in North Dakota

In [69]:
# State population df
ND_fertility_df = fertility_county.query(where="STATE='North Dakota'", as_df=True)
# ND_fertility_df = ND_featureset.sdf
ND_fertility_df.head()
Out[69]:
OBJECTID COUNTYNS GEOID ALAND AWATER NAME State B13016_001E B13016_001M B13016_002E B13016_002M B13016_003E B13016_003M B13016_004E B13016_004M B13016_005E B13016_005M B13016_006E B13016_006M B13016_007E B13016_007M B13016_008E B13016_008M B13016_009E B13016_009M B13016_010E B13016_010M B13016_011E B13016_011M B13016_012E B13016_012M B13016_013E B13016_013M B13016_014E B13016_014M B13016_015E B13016_015M B13016_016E B13016_016M B13016_017E B13016_017M B13016_calc_pctBirthsE B13016_calc_pctBirthsM B13016_calc_num15to19E B13016_calc_num15to19M B13016_calc_pct15to19E B13016_calc_pct15to19M B13016_calc_num20to24E B13016_calc_num20to24M B13016_calc_pct20to24E B13016_calc_pct20to24M B13016_calc_num25to29E B13016_calc_num25to29M B13016_calc_pct25to29E B13016_calc_pct25to29M B13016_calc_num30to34E B13016_calc_num30to34M B13016_calc_pct30to34E B13016_calc_pct30to34M B13016_calc_num35to39E B13016_calc_num35to39M B13016_calc_pct35to39E B13016_calc_pct35to39M B13016_calc_num40to44E B13016_calc_num40to44M B13016_calc_pct40to44E B13016_calc_pct40to44M B13016_calc_num45to50E B13016_calc_num45to50M B13016_calc_pct45to50E B13016_calc_pct45to50M Shape__Area Shape__Length SHAPE
0 1991 01034210 38001 2557960600 2833854 Adams County North Dakota 446 58 19 20 12 17 0 9 0 9 0 9 7 11 0 9 0 9 427 65 37 24 67 29 53 24 56 21 48 23 103 37 63 31 4.3 4.480234 49 29 24.5 31.521266 67 30 0.0 13.432836 53 26 0.0 16.981132 56 23 0.0 16.071429 55 25 12.7 19.145034 103 38 0.0 8.737864 63 32 0.0 14.285714 5.324157e+09 330639.744151 {'rings': [[[-11354354.1023, 5813340.1041], [-...
1 1992 01034225 38003 3863107435 56591279 Barnes County North Dakota 2234 110 173 73 0 15 5 10 124 63 18 22 26 35 0 15 0 15 2061 127 391 64 317 79 216 70 267 62 238 73 274 53 358 48 7.7 3.257869 391 66 0.0 3.836317 322 80 1.6 3.081535 340 94 36.5 15.545781 285 66 6.3 7.579469 264 81 9.8 12.908628 274 55 0.0 5.474453 358 50 0.0 4.189944 8.403601e+09 374027.679170 {'rings': [[[-10904992.8409, 5981421.7966], [-...
2 1993 01034216 38005 3596568808 131708144 Benson County North Dakota 1390 16 128 33 8 9 68 24 20 13 18 11 7 6 7 7 0 13 1262 37 202 21 181 25 178 13 166 12 155 24 140 25 240 16 9.2 2.364013 210 23 3.8 4.265356 249 35 27.3 8.841183 198 18 10.1 6.501124 184 16 9.8 5.917430 162 25 4.3 3.643182 147 26 4.8 4.686829 240 21 0.0 5.416667 8.343651e+09 537806.413896 {'rings': [[[-11075502.1995, 6168791.7355], [-...
3 1994 01035616 38007 2975481852 11990484 Billings County North Dakota 174 36 5 5 0 9 0 9 3 4 0 9 2 3 0 9 0 9 169 35 20 19 12 11 10 10 33 15 32 13 14 11 48 16 2.9 2.872377 20 21 0.0 45.000000 12 14 0.0 75.000000 13 11 23.1 23.779327 33 17 0.0 27.272727 34 13 5.9 8.532061 14 14 0.0 64.285714 48 18 0.0 18.750000 6.425825e+09 369444.155012 {'rings': [[[-11476951.3137, 5995895.8254], [-...
4 1995 01034227 38009 4321196493 74864998 Bottineau County North Dakota 1204 49 68 43 0 13 11 14 16 14 24 29 17 21 0 13 0 13 1136 67 163 37 159 43 117 15 141 27 175 49 132 37 249 42 5.6 3.565728 163 39 0.0 7.975460 170 45 6.5 8.055208 133 21 12.0 10.353515 165 40 14.5 17.218402 192 53 8.9 10.660919 132 39 0.0 9.848485 249 44 0.0 5.220884 1.011984e+10 483713.596470 {'rings': [[[-11152289.0833, 6274727.6819], [-...

Dataframe for OB-GYN providers in North Dakota by County

In [70]:
obgyn_ND_df = obgyn_df[obgyn_df['regionabbr']=='ND']
obgyn_ND_df.head()
Out[70]:
objectid user_npi user_entity_type user_provider_gender user_taxonomy_code_1 user_full_address postal city subregion region regionabbr SHAPE
58458 4972067 1.851398e+09 Individual M 207VM0101X 801 BROADWAY N, , FARGO, ND 58122 58102 Fargo Cass County North Dakota ND {'x': -96.78767519099995, 'y': 46.884720309000...
58459 4972187 1.366440e+09 Individual M 207V00000X 1000 SOUTH COLUMBIA ROAD, , GRAND FORKS, ND 58... 58201 Grand Forks Grand Forks County North Dakota ND {'x': -97.06651079899996, 'y': 47.912072701000...
58460 4972639 1.164496e+09 Individual M 207V00000X 1 HOSPITAL ROAD NORTH, , BELCOURT, ND 58316 58316 Belcourt Rolette County North Dakota ND {'x': -99.74991768599995, 'y': 48.838085857000...
58461 4972840 1.982658e+09 Individual M 207V00000X 1000 E ROSSER AVE, , BISMARCK, ND 585014414 58501 Bismarck Burleigh County North Dakota ND {'x': -100.77651963899996, 'y': 46.80847046400...
58462 4972850 1.093769e+09 Individual M 207V00000X 1000 E ROSSER AVE, , BISMARCK, ND 585014414 58501 Bismarck Burleigh County North Dakota ND {'x': -100.77651963899996, 'y': 46.80847046400...
In [71]:
obgyn_ND_df.shape
Out[71]:
(102, 12)
In [72]:
# Create dataframe of obgyn provider counts by county
obgyn_NDcounty_df = pd.DataFrame(obgyn_ND_df['subregion'].value_counts().reset_index().values, columns=['County','Provider_Count'])
obgyn_NDcounty_df.head()
Out[72]:
County Provider_Count
0 Cass County 37
1 Burleigh County 22
2 Grand Forks County 13
3 Ward County 10
4 Williams County 8

Merge Provider Count and Population Dataframes

In [73]:
# Merge provider count and women data at county level for ND
county_obgyn_df = pd.merge(ND_fertility_df,obgyn_NDcounty_df,left_on='NAME', right_on='County',how='left')
In [74]:
# Look at null values
county_obgyn_df[county_obgyn_df['Provider_Count'].isnull()][['NAME','Provider_Count']]
Out[74]:
NAME Provider_Count
0 Adams County NaN
1 Barnes County NaN
2 Benson County NaN
3 Billings County NaN
4 Bottineau County NaN
5 Bowman County NaN
6 Burke County NaN
9 Cavalier County NaN
10 Dickey County NaN
11 Divide County NaN
12 Dunn County NaN
13 Eddy County NaN
14 Emmons County NaN
15 Foster County NaN
16 Golden Valley County NaN
18 Grant County NaN
19 Griggs County NaN
20 Hettinger County NaN
21 Kidder County NaN
22 LaMoure County NaN
23 Logan County NaN
24 McHenry County NaN
25 McIntosh County NaN
26 McKenzie County NaN
27 McLean County NaN
28 Mercer County NaN
29 Morton County NaN
30 Mountrail County NaN
31 Nelson County NaN
32 Oliver County NaN
33 Pembina County NaN
34 Pierce County NaN
35 Ramsey County NaN
36 Ransom County NaN
37 Renville County NaN
40 Sargent County NaN
41 Sheridan County NaN
43 Slope County NaN
45 Steele County NaN
47 Towner County NaN
48 Traill County NaN
49 Walsh County NaN
51 Wells County NaN

We can see that:

  • 43 out of 53 counties in ND do not have any Obgyn healthcare providers.

We will replace NaN values for Provider Count in these counties with 1 to plot them on the map to see which counties have highest number of mothers per provider.

In [75]:
# Replace NaN with 1
county_obgyn_df['Provider_Count'].replace(np.nan,1,inplace=True)
In [76]:
# Create new columns that shows provider by women pop
county_obgyn_df['women_per_prov'] = county_obgyn_df['B13016_001E']/county_obgyn_df['Provider_Count']
# Create new columns that shows provider by mother pop
county_obgyn_df['mother_per_prov'] = county_obgyn_df['B13016_002E']/county_obgyn_df['Provider_Count']
In [77]:
# Arrange dataframe by mother_per_prov descending and then B13016_002E (women who gave birht) descending 
county_obgyn_df = county_obgyn_df.sort_values(by=['mother_per_prov','B13016_002E'], ascending=False)[['OBJECTID','SHAPE','NAME','Provider_Count','B13016_001E','B13016_002E','women_per_prov','mother_per_prov']]
county_obgyn_df.columns = ['OBJECTID','SHAPE','Name','Provider_Count', "Total Women (15 to 50)","Women who had birth (past 12 months)", 'women_per_prov', 'mother_per_prov']
county_obgyn_df.head()
Out[77]:
OBJECTID SHAPE Name Provider_Count Total Women (15 to 50) Women who had birth (past 12 months) women_per_prov mother_per_prov
29 2020 {'rings': [[[-11236143.3632, 5939274.8243], [-... Morton County 1 6575 463 6575.0 463.0
38 2029 {'rings': [[[-10773989.0107, 5881971.3849], [-... Richland County 1 3459 231 3459.0 231.0
1 1992 {'rings': [[[-10904992.8409, 5981421.7966], [-... Barnes County 1 2234 173 2234.0 173.0
46 2037 {'rings': [[[-11050272.8328, 5995646.2405], [-... Stutsman County 2 4275 303 2137.5 151.5
26 2017 {'rings': [[[-11446814.9108, 6127454.1041], [-... McKenzie County 1 2528 150 2528.0 150.0

Plot Shortage

In [84]:
obgyn_countyshortage_map = gis.map('North Dakota, USA', zoomlevel=6)
obgyn_countyshortage_map

We can see that:

  • Morton County seems to be the worst with 463 women who gave birth but no provider.
Define Renderer
In [79]:
# Define Renderer
obgynCountyTest = {"renderer": { #This tells python to use JS autocasting
                 "type": "classBreaks",  
                 "field":"mother_per_prov",
                 "transparency":.5,
                 "minValue":1}}
In [80]:
# Define Manual Class breaks
obgynCountyTest['renderer']["classBreakInfos"] = [{
  "classMaxValue": 100.00,
  "label": "0 - 100.00",
  "description": "0 - 100.00",
  "symbol": {
    "type": "esriSFS",
    "style": "esriSFSSolid",
    "color": [255,247,236,178.5],
    "outline": {
      "style": "esriSLSSolid",
      "type": "esriSLS",
      "color": [128,128,128,255],
      "width": 2
    }
  }
}, {
  "classMaxValue": 200.00,
  "label": "100.001 - 200.00",
  "description": "100.001 - 200.00",
  "symbol": {
    "type": "esriSFS",
    "style": "esriSFSSolid",
    "color": [253,220,174,178.5],
    "outline": {
      "style": "esriSLSSolid",
      "type": "esriSLS",
      "color": [128,128,128,255],
      "width": 2
    }
  }
}, {
  "classMaxValue": 300.00,
  "label": "200.001 - 300.00",
  "description": "200.001 - 300.00",
  "symbol": {
    "type": "esriSFS",
    "style": "esriSFSSolid",
    "color": [252,177,123,178.5],
    "outline": {
      "style": "esriSLSSolid",
      "type": "esriSLS",
      "color": [128,128,128,255],
      "width": 2
    }
  }
}, {
  "classMaxValue": 400.00,
  "label": "300.001 - 400.00",
  "description": "300.001 - 400.00",
  "symbol": {
    "type": "esriSFS",
    "style": "esriSFSSolid",
    "color": [241,109,75,178.5],
    "outline": {
      "style": "esriSLSSolid",
      "type": "esriSLS",
      "color": [128,128,128,255],
      "width": 2
    }
  }
}, {
  "classMaxValue": 500.00,
  "label": "400.001 - 500.00",
  "description": "400.001 - 500.00",
  "symbol": {
    "type": "esriSFS",
    "style": "esriSFSSolid",
    "color": [200,28,18,178.5],
    "outline": {
      "style": "esriSLSSolid",
      "type": "esriSLS",
      "color": [128,128,128,255],
      "width": 2
    }
  }
}]
Plot Map
In [81]:
# Plot Map using defined Renderer
obgyn_countyshortage_map.remove_layers()
county_obgyn_df.spatial.plot(map_widget=obgyn_countyshortage_map, renderer=obgynCountyTest['renderer'])
Out[81]:
True
In [82]:
#### FOR AUTOMATIC RENDERING - manual class breaks are not used

# county_obgyn_df.spatial.plot(map_widget=obgyn_countyshortage_map,
#         renderer_type='c',  # for class breaks renderer
#         method='esriClassifyNaturalBreaks',  # classification algorithm
#         class_count=5,  # choose the number of classes
#         col='mother_per_prov',  # numeric column to classify
#         cmap='OrRd',  # color map to pick colors from for each class
#         alpha=0.7 ,  # specify opacity
#        )
In [83]:
obgyn_countyshortage_map.legend=True

Morton County Infographic

To visualize high level key facts about a geography and gain greater insights, various infographics can be created using ArcGIS Business Analyst. Learn more about ArcGIS Business Analyst.

The image below shows an infographic of Health and Insurance Statistics for Morton County, ND.

North Dakota has the highest number of women (15 to 50) who had a birth in the past 12 months per OBGYN provider.

- There were ~112 mothers and 1655 women per provider in ND compared to ~31 mothers and 662 women per provider in DC.
- 43 out of 53 counties in ND do not have any OBGYN healthcare provider.
- Morton County seems to be the worst with 463 women who gave birth but no provider.

Summary

To summarize, we explored the shortage of Mental Health and OBGYN Providers accross United States. We saw that:

  1. Mississippi has the highest number of people per Mental Healthcare Provider
    • There were ~2754 people per mental healthcare provider in Mississippi compared to ~364 people per provider in DC.
    • 18 counties in Mississippi do not have any mental healthcare provider.
    • Marshall County seems to be the worst with 1 provider for 37137 people. Some other counties with high population per provider are Pontotoc, Neshoba, Scott and Union.
  1. North Dakota has the highest number of women (15 to 50) who had a birth in the past 12 months per OBGYN provider.
    • There were ~112 mothers and 1655 women per provider in ND compared to ~31 mothers and 662 women per provider in DC.
    • 43 out of 53 counties in ND do not have any OBGYN healthcare provider.
    • Morton County seems to be the worst with 463 women who gave birth but no provider.